
import pymysql

def init_table():
	
	#登陆数据库管理系统
	db = pymysql.connect(host='localhost',user='root',password='',charset='utf8')
	#获取数据库操作游标
	cursor = db.cursor()
	
	try:
		#创建stubase数据库
		cursor.execute('DROP DATABASE IF EXISTS stubase')
		cursor.execute('CREATE DATABASE stubase')
		cursor.execute('USE stubase')
		#创建stu表
		cursor.execute('DROP TABLE IF EXISTS stu')
		sql = '''CREATE TABLE stu(
					sid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
					name VARCHAR(32) NOT NULL,
					age TINYINT UNSIGNED NOT NULL,
					classid VARCHAR(100))'''
		#执行sql语句
		cursor.execute(sql)

		#向stu表中添加数据
		sql='''INSERT INTO stu(name,age,classid) 
				VALUES('zhangsan',25,'python02'),
					  ('lisi',21,'python03'),
					  ('wangwu',22,'python04')'''
		
		m = cursor.execute(sql)
		
		#提交事务
		db.commit()
		#print('成功添加条数：'，m)
	
	except Exception as err:
		#事务回滚
		db.rollback()
		print('SQL执行错误，原因：',err)

	#关闭数据库连接	
	db.close()		


class Stu:
	'''定义学生信息表类'''

	def __init__(self,table_name='stu'):
		'''构造方法实现数据库连接'''

		self.name = table_name
		self.db = pymysql.connect(host='localhost',user='root',password='',db='stubase',charset='utf8')
		self.cursor = self.db.cursor()		

	def findAll(self):
		'''查询记录'''

		sql = "SELECT sid,name,age,classid FROM " + self.name
		try:
			m = self.cursor.execute(sql) #self.cursor.rowcount
			#print('查询到%s条记录'%m)
			return self.cursor.fetchall()
		except Exception as err:
			print('SQL执行错误，原因：',err)
		

	def delete(self,id):
		'''删除记录'''

		sql = "DELETE FROM " + self.name + " WHERE sid=%d" %id
		try:
			m = self.cursor.execute(sql)
			self.db.commit()
			print('成功删除%s条记录' %m)
		except Exception as err:
			self.db.rollback()
			print('SQL执行错误，原因：',err)

	def insert(self,data):
		'''插入记录'''

		sql = "INSERT INTO "+self.name+"(name,age,classid) VALUES('%s','%d','%s')"%data
		try:
			m = self.cursor.execute(sql)
			self.db.commit()
			print('成功添加%s条记录' %m)
		except Exception as err:
			self.db.rollback()
			print('SQL执行错误，原因：',err)

	def __del__(self):
		'''析构方法关闭数据库连接'''
		self.db.close()